package tool;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Map;
import java.util.Properties;
import java.util.Queue;

import dataStruct.DataStoreTask;
import dataStruct.DataStoreTaskBag;
import dataStruct.EnumHeartBeatType;
import dataStruct.FtpServerInfo;

public class MysqlApi {
	public MysqlApi(Properties proper)
	{
		try {
			Class.forName("com.mysql.jdbc.Driver");// 加载Mysql数据驱动
			String mysqlAdress = proper.getProperty("mysqlAdress");
			String mysqlPort = proper.getProperty("mysqlPort");
			String mysqlUser = proper.getProperty("mysqlUser");
			String mysqlPassword = proper.getProperty("mysqlPassword");
			String mysqlDatabaseName = proper.getProperty("mysqlDatabaseName");

			System.out.println("jdbc:mysql://" + mysqlAdress + ":" + mysqlPort
					+ "/" + mysqlDatabaseName + mysqlUser + mysqlPassword);

			con = DriverManager.getConnection("jdbc:mysql://" + mysqlAdress
					+ ":" + mysqlPort + "/" + mysqlDatabaseName, mysqlUser,
					mysqlPassword);// 创建数据连接
			statement = con.createStatement();
		} catch (Exception e) {
			System.out.println("数据库连接失败" + e.getMessage());
		}
		
	}
	public  static Statement create_statement()
			throws SQLException {
		return con.createStatement();
	}

	public static Statement get_statement() throws SQLException {
		return statement;
	}

	public synchronized static String get_current_dir_id_by_path(
			Statement state, String parentDirId, String childPath)
			throws SQLException {
		if (childPath.equals("")) {
			return parentDirId;
		}
		// debug
		// System.out.println(String.format("MysqlApi.java:\t parentDirId = %s\tchildPath=%s",
		// parentDirId,childPath));
		String subChildPath = childPath.substring(1);
		int mark;
		if ((mark = subChildPath.indexOf('/')) != -1) {
			String childDirName = subChildPath.substring(0, mark);
			String childDirId = get_child_dir_id(state, parentDirId,
					childDirName);
			if (childDirId == null)
				return null;

			String childChildPath = subChildPath.substring(mark);
			return get_current_dir_id_by_path(state, childDirId, childChildPath);
		} else {
			return get_child_dir_id(state, parentDirId, subChildPath);
		}
	}

	private synchronized static String get_child_dir_id(Statement state,
			String parentDirId, String childDirName) throws SQLException {
		String query = String
				.format("select dir_id from user_dir left join dir_info on user_dir.dir_id=dir_info.dir_id "
						+ "where user_dir.parent_dir_id='%s' and dir_info.dir_name='%s'",
						parentDirId, childDirName);
		ResultSet resultSet = state.executeQuery(query);
		if (resultSet.next())
			return resultSet.getString(1);
		return null;
	}

	public synchronized static String get_home_dir_id_by_user_name(
			Statement state, String userName) throws SQLException {
		// TODO Auto-generated method stub
		String queryUserInfoSql = String.format(
				"select user_home_dir_id from user_info where user_name='%s';",
				userName);

		// debug
		// System.out.println(String.format("MysqlApi.java:\t queryUserInfoSql = %s",
		// queryUserInfoSql));
		ResultSet resultSet = state.executeQuery(queryUserInfoSql);
		if (resultSet.next()) {
			// debug
			// System.out.println(String.format("MysqlApi.java:\t resultSet.getString(1) = %s",
			// resultSet.getString(1)));
			return resultSet.getString(1);
		}

		System.out.println("your access mysql error:" + queryUserInfoSql);
		return null;
	}

	public synchronized static String insert_chunk_info(Statement state,
			String fileMd5, Long offset, Long size) throws SQLException {
		String insertChunkInfo = String
				.format("insert into chunk_info (file_md5, chunk_offset, chunk_size) values('%s', '%s ', '%s');",
						fileMd5, offset.toString(), size.toString());

		// debug
		System.out.println(String.format(
				"insertChunkInfo = %s (MysqlApi.java)", insertChunkInfo));

		if (state.executeUpdate(insertChunkInfo) != 1)
			return null;

		String getChunkIdSql = String
				.format("select max(chunk_id) from chunk_info; ");

		// debug
		System.out.println(String.format("1getChunkIdSql = %s (MysqlApi.java)",
				getChunkIdSql));

		ResultSet resultSet = state.executeQuery(getChunkIdSql);

		System.out.println(String.format("2getChunkIdSql = %s (MysqlApi.java)",
				getChunkIdSql));

		if (resultSet.next()) {
			// debug
			System.out.println(String.format(
					" resultSet.getString(1)= %s (MysqlApi.java)",
					resultSet.getString(1)));
			return resultSet.getString(1);
		}
		return null;
	}

	public synchronized static String insert_slaver_chunk(Statement state,
			String chunkId) throws SQLException {
		// TODO Auto-generated method stub
		String insertSlaverChunk = String.format(
				"insert into slaver_chunk(chunk_id) values ('%s');", chunkId);

		// debug
		System.out.println(String.format(
				"insertSlaverChunk = %s (MysqlApi.java)", insertSlaverChunk));
		if (state.executeUpdate(insertSlaverChunk) != 1)
			return null;

		String selectSlaverChunk = String
				.format("select max(slaver_chunk_id) from  slaver_chunk");
		// debug
		System.out.println(String.format(
				"selectSlaverChunk = %s (MysqlApi.java)", selectSlaverChunk));

		ResultSet resultSet = state.executeQuery(selectSlaverChunk);

		if (resultSet.next())
			return resultSet.getString(1);
		return null;
	}

	public synchronized static Boolean insert_slaver_info(Statement state,
			int slaverId, String slaverWorkplace) throws SQLException {
		// TODO Auto-generated method stub
		String selectSlaverInfoSql = String.format(
				"select count(*) from slaver_info where slaver_id='%s';",
				slaverId);

		// debug
		System.out.println(String
				.format("selectSlaverInfoSql = %s (MysqlApi.java)",
						selectSlaverInfoSql));
		ResultSet resultSet = state.executeQuery(selectSlaverInfoSql);
		if (resultSet.next())
			return true;

		String insertSlaverInfoSql = String
				.format("insert into slaver_info (slaver_id, slaver_workplace) values ('%s', '%s');",
						slaverId, slaverWorkplace);

		// debug
		System.out.println(String
				.format("insertSlaverInfoSql = %s (MysqlApi.java)",
						insertSlaverInfoSql));
		if (state.executeUpdate(insertSlaverInfoSql) == 1)
			return true;
		return false;
	}

	public synchronized static Boolean update_userCount_fileInfo(
			Statement state, String fileMd5, long num) throws SQLException {
		Long userCount = null;
		String selectFileInfo = String.format(
				"select user_count from file_info where file_md5='%s' ;",
				fileMd5);
		ResultSet resultSet = state.executeQuery(selectFileInfo);
		if (resultSet.next())
			userCount = resultSet.getLong(1);
		else
			return false;
		userCount += new Long(num);

		// debug
		// System.out.println(String.format("MysqlApi.java:\t userCount = %s",
		// userCount));
		String updateFileInfo;
		if (userCount >= 0)
			updateFileInfo = String
					.format("update file_info set user_count='%s' where file_md5='%s';",
							userCount.toString(), fileMd5);
		else
			updateFileInfo = String.format(
					"delete from file_info  where file_md5='%s';", fileMd5);

		if (state.executeUpdate(updateFileInfo) == 1)
			return true;
		return false;
	}

	public synchronized static Boolean update_slaverId_slaverChunk(
			Statement state, String slaverChunkId, int slaverId, String status)
			throws SQLException {
		// TODO Auto-generated method stub
		String updateSlaverId = String.format("update slaver_chunk "
				+ "set slaver_id='%s' , status='%s' "
				+ "where slaver_chunk_id='%s';", slaverId, status,
				slaverChunkId);

		// debug
		System.out.println(String.format("updateSlaverId = %s (MysqlApi.java)",
				updateSlaverId));
		if (state.executeUpdate(updateSlaverId) == 1) {
			return true;
		}
		return false;
	}

	public synchronized static Boolean update_slaverCount_chunkInfo(
			Statement state, String chunkId, int num) throws SQLException {
		// TODO Auto-generated method stub
		Long slaverCount = null;
		String selectChunkInfo = String.format(
				"select slaver_count from chunk_info where  chunk_id='%s' ;",
				chunkId);

		// debug
		System.out.println(String.format(
				"selectChunkInfo = %s (MysqlApi.java)", selectChunkInfo));

		ResultSet resultSet = state.executeQuery(selectChunkInfo);
		if (resultSet.next()) {
			slaverCount = resultSet.getLong(1);
		} else {
			System.out.println(String.format(
					"selectChunkInfo  has no answer (MysqlApi.java)"));
			return false;
		}

		slaverCount += new Long(num);

		String updateChunkInfo;
		if (slaverCount >= 0)
		{
			updateChunkInfo = String
					.format("update chunk_info set slaver_count='%s' where chunk_id='%s';",
							slaverCount.toString(), chunkId);
		}
		else
		{
			updateChunkInfo = String.format(
					"delete from chunk_info  where chunk_id='%s';", chunkId);
		}
		System.out.println(String.format(
				"updateChunkInfo = %s (MysqlApi.java)", updateChunkInfo));
		if (state.executeUpdate(updateChunkInfo) == 1) {
			return true;
		}
		
		return false;
	}

	public static String get_fileMd5_by_parentDirId_and_fileName(
			Statement state, String currentDirId, String fileName)
			throws SQLException {
		// TODO Auto-generated method stub
		String selectFileMd5 = String
				.format("select file_md5 from user_file where parent_dir_id='%s' and file_name='%s'",
						currentDirId, fileName);

		// debug
		System.out.println(String.format("selectFileMd5 = %s (MysqlApi.java)",
				selectFileMd5));

		ResultSet resultSet = state.executeQuery(selectFileMd5);
		if (resultSet.next()) {
			System.out.println(String.format("fileMd5 ='%s' (MysqlApi.java)",
					resultSet.getString(1)));
			return resultSet.getString(1);
		} else {
			System.out
					.println(String
							.format("dont exsit the md5 parent_dir_id='%s' and fileName='%s' (MysqlApi.java)",
									currentDirId, fileName));
			return null;
		}
	}

	
	public static boolean upload_to_ftp(Statement state, String fileMd5,
			FtpServerInfo ftpServerInfo,
			Map<String, DataStoreTaskBag> dataStoreTaskSlaverChunkMap,
			Queue<String> dataStoreTaskSlaverChunkQueue) throws SQLException {
		Long fileSize = null;
		String selectFileSize = String
				.format("select file_size  from file_info where file_md5='%s'",
						fileMd5);
		ResultSet resultSet0 = state.executeQuery(selectFileSize);
		if (resultSet0.next()) {
			fileSize = resultSet0.getLong(1);
		}

		String selectChunkInfo = String
				.format("select chunk_id, chunk_offset, chunk_size  from chunk_info where file_md5='%s'",
						fileMd5);
		ResultSet resultSet = state.executeQuery(selectChunkInfo);

		// debug
		while(resultSet.next())
		{
			//debug
			System.out
					.println(String
							.format("selectChunkInfo = %s (MyslqApi.java)",
									selectChunkInfo));
			
			String chunkId = resultSet.getString(1);

			DataStoreTask dataStoreTask = new DataStoreTask();
			dataStoreTask.chunkId = chunkId;
			dataStoreTask.ftpServerInfo = ftpServerInfo;
			dataStoreTask.heartBeatType = EnumHeartBeatType.RESPONSE_UPLOAD_FILE;
			dataStoreTask.offset = resultSet.getLong(2);
			dataStoreTask.size = resultSet.getLong(3);
			dataStoreTask.totalSize = fileSize;

			// debug
			System.out
					.println(String
							.format("chunkId = %s , offset = %d, size = %d is uploading(MyslqApi.java)",
									chunkId, dataStoreTask.offset,
									dataStoreTask.size));

			// debug
			String selectSlaverChunkId = String
					.format("select slaver_chunk_id,slaver_id from slaver_chunk where chunk_id='%s' limit 1",
							chunkId);

			Statement state1 =con.createStatement();
			ResultSet resultSet1 = state1.executeQuery(selectSlaverChunkId);
			if (resultSet1.next() == false) {
				System.out
						.println(String
								.format("Error: chunkId = %s dont store in slaver(MyslqApi.java)",
										chunkId));
				return false;
			}
			String slaverChunkId = resultSet1.getString(1);
			Integer slaverId = resultSet1.getInt(2);

			DataStoreTaskBag dataStoreTaskBag = new DataStoreTaskBag();

			dataStoreTaskBag.dataStoreTask = dataStoreTask;
			dataStoreTaskBag.slaverId = slaverId;
			dataStoreTaskBag.startTime = new Long(0); //System.currentTimeMillis();
			dataStoreTaskBag.isSlaverDown = false;
			dataStoreTaskSlaverChunkMap.put(slaverChunkId, dataStoreTaskBag);
			dataStoreTaskSlaverChunkQueue.offer(slaverChunkId);

			// debug
			System.out
					.println(String
							.format("slaverChunkId = %s had insert into dataStoreTaskSlaverChunk Queue and Map(MyslqApi.java)",
									slaverChunkId));
		}
		System.out.println(String.format("***************************"));
		return true;
	}

	public static boolean wordcount_upload_to_ftp(Statement state, String fileMd5,
			FtpServerInfo ftpServerInfo,
			Map<String, DataStoreTaskBag> dataStoreTaskSlaverChunkMap,
			Queue<String> dataStoreTaskSlaverChunkQueue) throws SQLException {
		Long fileSize = null;
		String selectFileSize = String
				.format("select file_size  from file_info where file_md5='%s'",
						fileMd5);
		ResultSet resultSet0 = state.executeQuery(selectFileSize);
		if (resultSet0.next()) {
			fileSize = resultSet0.getLong(1);
		}

		String selectChunkInfo = String
				.format("select chunk_id, chunk_offset, chunk_size  from chunk_info where file_md5='%s'",
						fileMd5);
		ResultSet resultSet = state.executeQuery(selectChunkInfo);

		// debug
		while(resultSet.next())
		{
			//debug
			System.out
					.println(String
							.format("selectChunkInfo = %s (MyslqApi.java)",
									selectChunkInfo));
			
			String chunkId = resultSet.getString(1);

			DataStoreTask dataStoreTask = new DataStoreTask();
			dataStoreTask.chunkId = chunkId;
			dataStoreTask.ftpServerInfo = ftpServerInfo;
			dataStoreTask.heartBeatType = EnumHeartBeatType.RESPONSE_UPLOAD_WORDCOUNT_FILE;
			dataStoreTask.offset = resultSet.getLong(2);
			dataStoreTask.size = resultSet.getLong(3);
			dataStoreTask.totalSize = fileSize;

			// debug
			System.out
					.println(String
							.format("chunkId = %s , offset = %d, size = %d is uploading(MyslqApi.java)",
									chunkId, dataStoreTask.offset,
									dataStoreTask.size));

			// debug
			String selectSlaverChunkId = String
					.format("select slaver_chunk_id,slaver_id from slaver_chunk where chunk_id='%s' limit 1",
							chunkId);

			Statement state1 =con.createStatement();
			ResultSet resultSet1 = state1.executeQuery(selectSlaverChunkId);
			if (resultSet1.next() == false) {
				System.out
						.println(String
								.format("Error: chunkId = %s dont store in slaver(MyslqApi.java)",
										chunkId));
				return false;
			}
			String slaverChunkId = resultSet1.getString(1);
			Integer slaverId = resultSet1.getInt(2);

			DataStoreTaskBag dataStoreTaskBag = new DataStoreTaskBag();

			dataStoreTaskBag.dataStoreTask = dataStoreTask;
			dataStoreTaskBag.slaverId = slaverId;
			dataStoreTaskBag.startTime = new Long(0); //System.currentTimeMillis();
			dataStoreTaskBag.isSlaverDown = false;
			dataStoreTaskSlaverChunkMap.put(slaverChunkId, dataStoreTaskBag);
			dataStoreTaskSlaverChunkQueue.offer(slaverChunkId);

			// debug
			System.out
					.println(String
							.format("slaverChunkId = %s had insert into dataStoreTaskSlaverChunk Queue and Map(MyslqApi.java)",
									slaverChunkId));
		}
		System.out.println(String.format("***************************"));
		return true;
	}


	private static Connection con = null; // 创建用于连接数据库的Connection对象
	private static Statement statement = null;
}
